ALTER PROCEDURE bd_proc_userProductionHour
	@yearMonth VARCHAR(max)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;

	--  异常扑捉机制
		BEGIN TRY

			--创建临时表
			CREATE TABLE #temp(
				user_serial VARCHAR(50),
				dep_serial VARCHAR(50),
				user_no VARCHAR(50),
				user_lname VARCHAR(50),
				cost_center VARCHAR(50),
				userZh VARCHAR(max),
				userZhMs VARCHAR(max),
				trans VARCHAR(50),
				year_month VARCHAR(max),
				day VARCHAR(max),
				dayValue VARCHAR(max)
			)

		-- 开启事务
			BEGIN TRAN

				--删除当前月份的记录
				delete from bd_kq_user_case where year_month = @yearMonth;

				--添加用户ID,日期,编号,时长到临时表
				INSERT INTO #temp (user_serial,dep_serial,user_no,user_lname,cost_center,userZh,userZhMs,trans,year_month,day,dayValue)
				select
				d.user_serial,
				d.user_dep,
				d.user_no,
				d.user_lname,
				d.cost_center,
				v.dep_no_add5+'/'+v.dep_no_add4+'/'+v.dep_no_add3+'/'+v.dep_no_add2+'/'+dep_no_add as userZh,
				v.dep_name5+'/'+v.dep_name4+'/'+v.dep_name3+'/'+v.dep_name2+'/'+v.dep_name as userZhMs,
				'N',
				@yearMonth,
				'day' + CONVERT (VARCHAR(7),DATEPART(dd,card_date)),
				actual_hours+ot_normal_hours+ot_weekend_hours+ot_holiday_hours
				from bd_kq_timecard k
				left join dt_user d on d.user_serial=k.user_serial
				left join view_bd_dep_level v on d.user_dep=v.dep_serial
				WHERE
					CONVERT(VARCHAR(7),card_date,120) = @yearMonth;

				--处理假类数据
				INSERT INTO bd_kq_userProductionHour
				SELECT
					user_serial,
					dep_serial,
					user_no,
					user_lname,
					cost_center,
					userZh,
					userZhMs,
					trans,
					year_month,
					day1,
					day2,
					day3,
					day4,
					day5,
					day6,
					day7,
					day8,
					day9,
					day10,
					day11,
					day12,
					day13,
					day14,
					day15,
					day16,
					day17,
					day18,
					day19,
					day20,
					day21,
					day22,
					day23,
					day24,
					day25,
					day26,
					day27,
					day28,
					day29,
					day30,
					day31
				FROM (
					SELECT *
					FROM dbo.#temp
				) t
				PIVOT (
					max(dayValue)
					FOR day IN (
							day1,
							day2,
							day3,
							day4,
							day5,
							day6,
							day7,
							day8,
							day9,
							day10,
							day11,
							day12,
							day13,
							day14,
							day15,
							day16,
							day17,
							day18,
							day19,
							day20,
							day21,
							day22,
							day23,
							day24,
							day25,
							day26,
							day27,
							day28,
							day29,
							day30,
							day31
					)
				) p;



		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	DROP TABLE #temp;
	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;